# 作者:小宇
# 2025年11月12日15时02分12秒
# 1225074067@qq.com
import pandas as pd
import json
import re
from pathlib import Path
import os

# 首先查看上传的Excel文件结构
file_path = './data/15物产 - 总数据和各朝代数据.xlsx'

# 查看Excel文件的所有sheet名称
excel_file = pd.ExcelFile(file_path)
print("Excel文件包含的sheet:")
for i, sheet in enumerate(excel_file.sheet_names):
    print(f"{i + 1}. {sheet}")

print("\n" + "=" * 50)

# 读取每个sheet的前几行数据，了解数据结构
for sheet_name in excel_file.sheet_names:
    print(f"\n正在查看sheet: {sheet_name}")
    try:
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        print(f"数据形状: {df.shape}")
        print(f"列名: {list(df.columns)}")
        print("前3行数据:")
        print(df.head(3))

        # 检查是否有"时期"相关的列或第一行是标题
        if df.shape[0] > 0:
            first_row = df.iloc[0]
            print(f"第一行数据类型: {first_row.dtype if hasattr(first_row, 'dtype') else '混合类型'}")

    except Exception as e:
        print(f"读取sheet {sheet_name} 时出错: {e}")

    print("-" * 30)


def extract_data_from_property_text(text):
    """从物产文本中提取关键信息"""
    if pd.isna(text) or not isinstance(text, str):
        return {
            'year': None,
            'population': None,
            'area': None,
            'product_categories': None,
            'key_products': None,
            'production_scale': None
        }

    result = {
        'year': None,
        'population': None,
        'area': None,
        'product_categories': [],
        'key_products': [],
        'production_scale': None
    }

    # 1. 提取年份信息
    year_patterns = [
        r'(\d{3,4})\s*年',
        r'公元\s*(\d{3,4})',
        r'顺治\s*\((\d{4})—(\d{4})\)',
        r'乾隆\s*\((\d{4})—(\d{4})\)',
        r'康熙\s*\((\d{4})—(\d{4})\)',
        r'雍正\s*\((\d{4})—(\d{4})\)',
        r'道光\s*\((\d{4})—(\d{4})\)',
        r'咸丰\s*\((\d{4})—(\d{4})\)',
        r'同治\s*\((\d{4})—(\d{4})\)',
        r'光绪\s*\((\d{4})—(\d{4})\)',
        r'宣统\s*\((\d{4})—(\d{4})\)'
    ]

    for pattern in year_patterns:
        matches = re.findall(pattern, text)
        if matches:
            for match in matches:
                if isinstance(match, tuple):
                    # 处理年号范围，取起始年份
                    if len(match) >= 1 and match[0].isdigit():
                        result['year'] = int(match[0])
                        break
                else:
                    if match.isdigit():
                        year = int(match)
                        # 过滤不合理的年份
                        if 1 <= year <= 2025:
                            result['year'] = year
                            break
            if result['year']:
                break

    # 2. 提取人口信息（如果有）
    population_patterns = [
        r'(\d+(?:\.\d+)?)\s*万户',
        r'(\d+(?:\.\d+)?)\s*万口',
        r'(\d+(?:\.\d+)?)\s*万人',
        r'人口.*?(\d+(?:\.\d+)?)\s*万',
        r'户.*?(\d+(?:\.\d+)?)\s*万',
        r'(\d+)\s*户',
        r'(\d+)\s*口'
    ]

    for pattern in population_patterns:
        match = re.search(pattern, text)
        if match:
            try:
                num = float(match.group(1))
                if '万' in pattern:
                    result['population'] = int(num * 10000)
                else:
                    result['population'] = int(num)
                break
            except:
                pass

    # 3. 提取面积信息（如果有）
    # 平方千米/平方公里
    area_match = re.search(r'(\d+(?:\.\d+)?)\s*(?:万\s*)?平方(?:千米|公里)', text)
    if area_match:
        try:
            area_value = float(area_match.group(1))
            if '万' in area_match.group(0):
                result['area'] = area_value * 10000  # 万平方千米转换
            else:
                result['area'] = area_value
        except:
            pass
    else:
        # 耕地面积（亩）
        area_match = re.search(r'耕地.*?(\d+(?:\.\d+)?)\s*(?:万\s*)?亩', text)
        if area_match:
            try:
                area_mu = float(area_match.group(1))
                if '万' in area_match.group(0):
                    area_mu *= 10000
                result['area'] = round(area_mu / 1500, 2)  # 1平方千米 = 1500亩
            except:
                pass

    # 4. 提取物产类别和关键物产
    # 常见物产类别关键词
    category_keywords = {
        '粮食作物': ['稻', '麦', '粟', '黍', '稷', '豆', '玉米', '高粱', '荞麦', '燕麦'],
        '经济作物': ['棉', '麻', '桑', '茶', '烟', '甘蔗', '油料', '芝麻', '花生', '油菜'],
        '蔬菜水果': ['菜', '果', '瓜', '桃', '梨', '苹果', '葡萄', '枣', '柿', '杏', '李', '蔬菜'],
        '畜牧养殖': ['牛', '羊', '马', '猪', '鸡', '鸭', '鹅', '畜牧', '养殖'],
        '水产渔业': ['鱼', '虾', '蟹', '贝', '水产', '渔业'],
        '手工业品': ['布', '绢', '绸', '缎', '瓷', '陶', '铁', '铜', '木', '竹', '手工'],
        '林业资源': ['木', '材', '松', '柏', '杉', '竹', '林', '木柴']
    }

    # 提取明确的物产类型（从文本中直接找"物产类型"相关内容）
    product_type_match = re.search(r'物产类型[:：]?\s*(.*?)(?=[。。；；，,；\n]|出处|$)', text)
    if product_type_match:
        product_types = [pt.strip() for pt in product_type_match.group(1).split('；') if pt.strip()]
        result['product_categories'].extend(product_types)

    # 基于关键词识别物产类别和关键物产
    text_lower = text.lower()
    for category, keywords in category_keywords.items():
        found_products = []
        for keyword in keywords:
            if keyword in text:
                found_products.append(keyword)
                if category not in result['product_categories']:
                    result['product_categories'].append(category)

        if found_products:
            result['key_products'].extend(found_products)

    # 去重
    result['product_categories'] = list(set(result['product_categories'])) if result['product_categories'] else None
    result['key_products'] = list(set(result['key_products'])) if result['key_products'] else None

    # 5. 提取生产规模信息
    scale_patterns = [
        r'(\d+)\s*庄',
        r'(\d+)\s*处',
        r'(\d+)\s*顷',
        r'(\d+)\s*亩',
        r'(\d+(?:\.\d+)?)\s*万(?:亩|顷)',
        r'大片',
        r'广泛',
        r'普遍',
        r'主要',
        r'重要'
    ]

    scale_info = []
    for pattern in scale_patterns:
        matches = re.findall(pattern, text)
        for match in matches:
            if match.isdigit():
                scale_info.append(
                    f"{match}{'庄' if '庄' in pattern else '处' if '处' in pattern else '顷' if '顷' in pattern else '亩'}")
            elif match in ['大片', '广泛', '普遍', '主要', '重要']:
                scale_info.append(match)

    if scale_info:
        result['production_scale'] = '、'.join(list(set(scale_info)))

    return result


def extract_property_data_from_excel(file_path):
    """从物产Excel文件中提取数据"""
    try:
        # 读取Excel文件的所有sheet
        excel_file = pd.ExcelFile(file_path)
        all_results = []

        print(f"开始处理Excel文件: {file_path}")
        print(f"文件包含 {len(excel_file.sheet_names)} 个sheet")

        for sheet_name in excel_file.sheet_names:
            print(f"\n正在处理sheet: {sheet_name}")

            # 读取当前sheet
            df = pd.read_excel(file_path, sheet_name=sheet_name)

            # 清理列名，处理可能的Unnamed列
            df.columns = [f'col_{i}' if 'Unnamed' in str(col) else str(col) for i, col in enumerate(df.columns)]

            # 查找时期列和物产列
            period_col = None
            property_col = None
            type_col = None
            source_col = None

            # 检查前几行，找到实际的列标题（处理可能的标题行问题）
            header_row = 0
            if df.shape[0] > 0:
                # 检查第一行是否是真正的标题
                first_row = df.iloc[0]
                if '时期' in str(first_row).replace('nan', '') or '物产' in str(first_row).replace('nan', ''):
                    # 如果第一行包含关键标题，重新设置列名
                    new_columns = []
                    for val in first_row:
                        if pd.notna(val):
                            if '时期' in str(val):
                                new_columns.append('时期')
                                period_col = len(new_columns) - 1
                            elif '物产' in str(val) and '类型' not in str(val):
                                new_columns.append('物产')
                                property_col = len(new_columns) - 1
                            elif '类型' in str(val) or '物产类型' in str(val):
                                new_columns.append('物产类型')
                                type_col = len(new_columns) - 1
                            elif '出处' in str(val):
                                new_columns.append('出处')
                                source_col = len(new_columns) - 1
                            else:
                                new_columns.append(f'col_{len(new_columns)}')
                        else:
                            new_columns.append(f'col_{len(new_columns)}')

                    df.columns = new_columns
                    # 跳过标题行
                    df = df.iloc[1:].reset_index(drop=True)
                else:
                    # 直接根据列内容判断
                    for i, col in enumerate(df.columns):
                        if '时期' in str(col) or any('时期' in str(val) for val in df[col].dropna().head(10)):
                            period_col = i
                        elif '物产' in str(col) or any('物产' in str(val) for val in df[col].dropna().head(10)):
                            property_col = i
                        elif '类型' in str(col) or any('类型' in str(val) for val in df[col].dropna().head(10)):
                            type_col = i
                        elif '出处' in str(col) or any('出处' in str(val) for val in df[col].dropna().head(10)):
                            source_col = i

            # 如果没有明确找到列，使用默认列位置（基于之前的观察）
            if period_col is None and len(df.columns) > 0:
                period_col = 0  # 第一列通常是时期
            if property_col is None and len(df.columns) > 1:
                property_col = 1  # 第二列通常是物产描述
            if type_col is None and len(df.columns) > 2:
                type_col = 2  # 第三列通常是物产类型
            if source_col is None and len(df.columns) > 3:
                source_col = 3  # 第四列通常是出处

            print(f"列映射: 时期列={period_col}, 物产列={property_col}, 类型列={type_col}, 出处列={source_col}")

            # 处理每一行数据
            sheet_results = []
            for idx, row in df.iterrows():
                try:
                    # 获取时期（朝代/年代）
                    period = row.iloc[period_col] if period_col is not None and period_col < len(row) else None
                    if pd.isna(period) or str(period).strip() == '':
                        continue
                    period = str(period).strip()

                    # 获取物产描述
                    property_desc = row.iloc[property_col] if property_col is not None and property_col < len(
                        row) else None
                    if pd.isna(property_desc) or str(property_desc).strip() == '':
                        continue
                    property_desc = str(property_desc).strip()

                    # 获取物产类型（如果有单独的列）
                    property_type = None
                    if type_col is not None and type_col < len(row) and pd.notna(row.iloc[type_col]):
                        property_type = str(row.iloc[type_col]).strip()

                    # 获取出处信息
                    source_info = None
                    if source_col is not None and source_col < len(row) and pd.notna(row.iloc[source_col]):
                        source_info = str(row.iloc[source_col]).strip()

                    # 提取详细数据
                    extracted_data = extract_data_from_property_text(property_desc)

                    # 整合物产类型信息
                    if property_type and extracted_data['product_categories']:
                        if property_type not in extracted_data['product_categories']:
                            extracted_data['product_categories'].append(property_type)
                            extracted_data['product_categories'] = list(set(extracted_data['product_categories']))
                    elif property_type:
                        extracted_data['product_categories'] = [property_type]

                    # 创建完整记录
                    record = {
                        'sheet_name': sheet_name,
                        'category': period,  # 时期/朝代
                        'year': extracted_data['year'],
                        'population': extracted_data['population'],
                        'area': extracted_data['area'],  # 单位：平方千米
                        'product_categories': extracted_data['product_categories'],  # 物产类别
                        'key_products': extracted_data['key_products'],  # 关键物产
                        'production_scale': extracted_data['production_scale'],  # 生产规模
                        'property_description': property_desc,  # 完整物产描述
                        'source': source_info  # 文献出处
                    }

                    # 只添加有意义的记录（至少有时期和物产描述）
                    sheet_results.append(record)

                except Exception as e:
                    print(f"处理第{idx + 1}行时出错: {e}")
                    continue

            all_results.extend(sheet_results)
            print(f"从 {sheet_name} 提取了 {len(sheet_results)} 条记录")

        print(f"\n总计提取了 {len(all_results)} 条有效记录")
        return all_results

    except Exception as e:
        print(f"读取Excel文件时出错: {e}")
        return []


def save_property_data_to_json(data, output_file):
    """将提取的物产数据保存为JSON文件"""
    try:
        # 创建输出目录（如果不存在）
        output_dir = os.path.dirname(output_file)
        if output_dir and not os.path.exists(output_dir):
            os.makedirs(output_dir, exist_ok=True)

        # 保存为JSON文件
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump(data, f, ensure_ascii=False, indent=2)

        print(f"\n数据已成功保存到: {output_file}")
        return True

    except Exception as e:
        print(f"保存JSON文件时出错: {e}")
        return False


def analyze_property_data(data):
    """分析提取的物产数据，生成统计信息"""
    if not data:
        print("没有可分析的数据")
        return None

    print("\n" + "=" * 60)
    print("物产数据统计分析报告")
    print("=" * 60)

    # 1. 按时期/朝代统计
    categories = {}
    for record in data:
        category = record['category']
        if category not in categories:
            categories[category] = 0
        categories[category] += 1

    print(f"\n1. 按时期/朝代分布:")
    sorted_categories = sorted(categories.items(), key=lambda x: x[1], reverse=True)
    for cat, count in sorted_categories:
        print(f"   {cat}: {count} 条记录")

    # 2. 按sheet统计
    sheets = {}
    for record in data:
        sheet = record['sheet_name']
        if sheet not in sheets:
            sheets[sheet] = 0
        sheets[sheet] += 1

    print(f"\n2. 按sheet分布:")
    for sheet, count in sheets.items():
        print(f"   {sheet}: {count} 条记录")

    # 3. 数据完整性统计
    has_year = 0
    has_population = 0
    has_area = 0
    has_categories = 0
    has_products = 0
    has_source = 0

    for record in data:
        if record['year'] is not None:
            has_year += 1
        if record['population'] is not None:
            has_population += 1
        if record['area'] is not None:
            has_area += 1
        if record['product_categories'] is not None and len(record['product_categories']) > 0:
            has_categories += 1
        if record['key_products'] is not None and len(record['key_products']) > 0:
            has_products += 1
        if record['source'] is not None and record['source'].strip() != '':
            has_source += 1

    total = len(data)
    print(f"\n3. 数据完整性统计:")
    print(f"   包含年份信息: {has_year}/{total} ({has_year / total * 100:.1f}%)")
    print(f"   包含人口信息: {has_population}/{total} ({has_population / total * 100:.1f}%)")
    print(f"   包含面积信息: {has_area}/{total} ({has_area / total * 100:.1f}%)")
    print(f"   包含物产类别: {has_categories}/{total} ({has_categories / total * 100:.1f}%)")
    print(f"   包含关键物产: {has_products}/{total} ({has_products / total * 100:.1f}%)")
    print(f"   包含文献出处: {has_source}/{total} ({has_source / total * 100:.1f}%)")

    # 4. 物产类别统计
    all_categories = {}
    for record in data:
        if record['product_categories']:
            for cat in record['product_categories']:
                if cat not in all_categories:
                    all_categories[cat] = 0
                all_categories[cat] += 1

    print(f"\n4. 物产类别分布 (前15名):")
    sorted_all_categories = sorted(all_categories.items(), key=lambda x: x[1], reverse=True)[:15]
    for cat, count in sorted_all_categories:
        print(f"   {cat}: {count} 条记录")

    # 5. 关键物产统计
    all_products = {}
    for record in data:
        if record['key_products']:
            for prod in record['key_products']:
                if prod not in all_products:
                    all_products[prod] = 0
                all_products[prod] += 1

    print(f"\n5. 关键物产分布 (前15名):")
    sorted_all_products = sorted(all_products.items(), key=lambda x: x[1], reverse=True)[:15]
    for prod, count in sorted_all_products:
        print(f"   {prod}: {count} 条记录")

    return {
        'total_records': total,
        'category_distribution': categories,
        'sheet_distribution': sheets,
        'data_completeness': {
            'year': has_year,
            'population': has_population,
            'area': has_area,
            'product_categories': has_categories,
            'key_products': has_products,
            'source': has_source
        },
        'product_category_distribution': all_categories,
        'key_product_distribution': all_products
    }


def main():
    """主函数：处理物产Excel数据并生成JSON"""
    # 配置文件路径
    input_file = './data/15物产 - 总数据和各朝代数据.xlsx'
    output_file = './outputs/property_data.json'

    print("=" * 60)
    print("开始处理中国古代物产数据")
    print("=" * 60)

    # 1. 提取数据
    print("\n第一步：从Excel文件中提取物产数据")
    property_data = extract_property_data_from_excel(input_file)

    if not property_data:
        print("未能提取到任何数据，程序终止")
        return

    # 2. 保存数据到JSON
    print("\n第二步：保存数据到JSON文件")
    save_success = save_property_data_to_json(property_data, output_file)

    if not save_success:
        print("保存文件失败，程序终止")
        return

    # 3. 数据分析
    print("\n第三步：数据分析")
    analysis_result = analyze_property_data(property_data)

    # 4. 显示示例数据
    print(f"\n第四步：显示前3条数据示例")
    print("-" * 60)
    for i, record in enumerate(property_data[:3], 1):
        print(f"\n示例 {i}:")
        print(f"时期/朝代: {record['category']}")
        print(f"年份: {record['year'] if record['year'] else '未提及'}")
        print(f"人口: {record['population'] if record['population'] else '未提及'}")
        print(f"面积: {record['area'] if record['area'] else '未提及'} 平方千米")
        print(f"物产类别: {', '.join(record['product_categories']) if record['product_categories'] else '未分类'}")
        print(f"关键物产: {', '.join(record['key_products']) if record['key_products'] else '未识别'}")
        print(f"生产规模: {record['production_scale'] if record['production_scale'] else '未提及'}")
        print(
            f"文献出处: {record['source'][:100] + '...' if record['source'] and len(record['source']) > 100 else record['source'] if record['source'] else '未标注'}")
        print(f"数据来源sheet: {record['sheet_name']}")
        print("-" * 40)

    print(f"\n处理完成！")
    print(f"生成的JSON文件包含 {len(property_data)} 条物产记录")
    print(f"文件位置: {output_file}")

    return property_data


# 运行主程序
if __name__ == '__main__':
    processed_data = main()